SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create procedure dbo.DnnProduct_GetSiteLog5

@PortalID int,
@PortalAlias nvarchar(50),
@StartDate datetime,
@EndDate datetime

as

select'UserAgent' = 
 case 
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 1%' then 'Internet Explorer 1'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 2%' then 'Internet Explorer 2'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 3%' then 'Internet Explorer 3'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 4%' then 'Internet Explorer 4'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 5%' then 'Internet Explorer 5'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 6%' then 'Internet Explorer 6'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE%' then 'Internet Explorer'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/1%' then 'Netscape Navigator 1'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/2%' then 'Netscape Navigator 2'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/3%' then 'Netscape Navigator 3'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/4%' then 'Netscape Navigator 4'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/5%' then 'Netscape Navigator 6+'
 else dbo.DnnProduct_SiteLog.UserAgent
 end,
 'Requests' = count(*),
 'LastRequest' = max(DateTime)
from dbo.DnnProduct_SiteLog
where PortalId = @PortalID
and dbo.DnnProduct_SiteLog.DateTime between @StartDate and @EndDate
group by case 
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 1%' then 'Internet Explorer 1'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 2%' then 'Internet Explorer 2'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 3%' then 'Internet Explorer 3'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 4%' then 'Internet Explorer 4'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 5%' then 'Internet Explorer 5'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 6%' then 'Internet Explorer 6'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE%' then 'Internet Explorer'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/1%' then 'Netscape Navigator 1'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/2%' then 'Netscape Navigator 2'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/3%' then 'Netscape Navigator 3'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/4%' then 'Netscape Navigator 4'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/5%' then 'Netscape Navigator 6+'
 else dbo.DnnProduct_SiteLog.UserAgent
 end
order by Requests desc









GO
